package com.zzl.services.impl;

import com.zzl.entity.PageBean;
import com.zzl.entity.Student;
import com.zzl.services.StudentServices;
import com.zzl.util.DateUtil;
import com.zzl.util.StringEmptyUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentServicrImpl implements StudentServices {


    @Override
    public int studentCount(Connection con, Student student, String bbirthday, String ebirthday) throws SQLException {
        StringBuffer sb=new StringBuffer("select count(*) as total from t_student s,t_grade g where s.gradeId=g.id");
        if(StringEmptyUtil.isNotEmpty(student.getStuNo())){
            sb.append(" and s.stuNo like '%"+student.getStuNo()+"%'");
        }

        if(StringEmptyUtil.isNotEmpty(student.getStuName())){
            sb.append(" and s.stuName like '%"+student.getStuName()+"%'");
        }
        if(StringEmptyUtil.isNotEmpty(student.getSex())){
            sb.append(" and s.sex ='"+student.getSex()+"'");
        }
        if(student.getGradeId()!=-1){
            sb.append(" and s.gradeId ='"+student.getGradeId()+"'");
        }

        if(StringEmptyUtil.isNotEmpty(bbirthday)){
            sb.append(" and TO_DAYS(s.birthday) >=TO_DAYS('"+bbirthday+"')");
        }
        if(StringEmptyUtil.isNotEmpty(ebirthday)){
            sb.append(" and TO_DAYS(s.birthday) <=TO_DAYS('"+ebirthday+"')");
        }
        PreparedStatement pstmt=con.prepareStatement(sb.toString());
        ResultSet rs=pstmt.executeQuery();
        if (rs.next()){
            return rs.getInt("total");
        } else {
            return 0;
        }
    }

    @Override
    public ResultSet studentList(Connection con, PageBean pageBean, Student student, String bbirthday, String ebirthday) throws SQLException {
        StringBuffer sb=new StringBuffer("select * from t_student s,t_grade g where s.gradeId=g.id");
        if(StringEmptyUtil.isNotEmpty(student.getStuNo())){
            sb.append(" and s.stuNo like '%"+student.getStuNo()+"%'");
        }

        if(StringEmptyUtil.isNotEmpty(student.getStuName())){
            sb.append(" and s.stuName like '%"+student.getStuName()+"%'");
        }
        if(StringEmptyUtil.isNotEmpty(student.getSex())){
            sb.append(" and s.sex ='"+student.getSex()+"'");
        }
        if(student.getGradeId()!=-1){
            sb.append(" and s.gradeId ='"+student.getGradeId()+"'");
        }

        if(StringEmptyUtil.isNotEmpty(bbirthday)){
            sb.append(" and TO_DAYS(s.birthday) >=TO_DAYS('"+bbirthday+"')");
        }
        if(StringEmptyUtil.isNotEmpty(ebirthday)){
            sb.append(" and TO_DAYS(s.birthday) <=TO_DAYS('"+ebirthday+"')");
        }
        if(pageBean!=null){
            sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
        }
        PreparedStatement pstmt= con.prepareStatement(sb.toString());
        return pstmt.executeQuery();
    }

    @Override
    public int studentDelete(Connection con, String delIds) throws SQLException {
        String sql="delete from t_student where stuId in("+delIds+")";
        PreparedStatement pstmt=con.prepareStatement(sql);
        return pstmt.executeUpdate();

    }

    @Override
    public int studentModify(Connection con, Student student) throws SQLException {
        String sql="update t_student set stuNo=?,stuName=?,sex=?,birthday=DATE_FORMAT(?,'%Y-%d-%m'),gradeId=?,email=?,stuDesc=? where stuId=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,student.getStuNo());
        pstmt.setString(2,student.getStuName());
        pstmt.setString(3,student.getSex());
        pstmt.setString(4,DateUtil.formatDate(student.getBirthday(),"yyyy-MM-dd"));
        pstmt.setInt(5,student.getGradeId());
        pstmt.setString(6,student.getEmail());
        pstmt.setString(7,student.getStuDesc());
        pstmt.setInt(8,student.getStuID());
        return pstmt.executeUpdate();
    }

    @Override
    public int studentAdd(Connection con, Student student) throws SQLException {
        String sql="insert into t_student (stuId,stuNo,stuName,sex,birthday,gradeId,email,stuDesc) values(null,?,?,?,DATE_FORMAT(?,'%Y-%d-%m'),?,?,?)";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,student.getStuNo());
        pstmt.setString(2,student.getStuName());
        pstmt.setString(3,student.getSex());
        pstmt.setString(4,DateUtil.formatDate(student.getBirthday(),"yyyy-MM-dd"));
        pstmt.setInt(5,student.getGradeId());
        pstmt.setString(6,student.getEmail());
        pstmt.setString(7,student.getStuDesc());
        return pstmt.executeUpdate();
    }

}
